import datetime
import openpyxl
import json
import os
from config import exclusion, owner_department


# mvn方式获取内网项目提供的'clientId','project','owner'信息
def get_user_list():
    cmd = 'cd /d d:/Program Files/apache-maven-3.8.1-bin/ins-scm-internal&mvn clean test -e "-Dtest=com.liepin.scm.internal.tickets.helper.GitlabClientHelperTest#projectInfo" "-DSystemRuntimeEnvironment=qa" "-Darea=QAPUB" "-Dconfig.client_id=20170" "-Dsurefire.useFile=false"'
    cmd_popen = os.popen(cmd)
    datalist = cmd_popen.readlines()
    cmd_popen.close()
    start_index = datalist.index('=====\n')
    end_index = datalist.index('+++++\n')
    datalist_1 = datalist[start_index + 2:end_index - 2]
    datalist_2 = []
    for i in datalist_1:
        new_i = i.strip('[')
        new_i = json.loads(new_i.strip(',\n'))
        # 由于部门名称无法通过内部项目提供，需要config.py文件提供并写入到每条记录中，最后组合为新的list
        for j in range(len(owner_department)):
            if new_i['owner'] == owner_department[j].get('owner'):
                new_i['department'] = owner_department[j].get('department')
            elif new_i['owner'] == '':
                new_i['department'] = ''
        datalist_2.append(new_i)
    # print(len(datalist_2), datalist_2)
    return datalist_2


# 读取最新接口调用敏感信息数据，添加相关列数据（说明超过1万次调用的原因、阈值上限、project、负责人、描述、是否合理、部门、主负责人、说明超过阈值的原因、new_count、存量/新发现），格式化为最终表格数据格式，输出list数据
def new_data(sheet_index):
    ws = wb.worksheets[sheet_index]
    nrows = ws.max_row
    ncols = ws.max_column
    # 获取配置文件config.py中exclusion，并将'clientid‘字段值组成exclusion_list
    exclusion_list = []
    for i in range(len(exclusion)):
        exclusion_list.append(exclusion[i].get('clientid'))
    # print(exclusion_list)

    # 由于日志统计后的表格存在合并单元格，所以需要计算出合并的单元格
    '''
    跨行（rowspan）的表格，根据(合并表格行数,合并表格列数):(合并表格第一行行数,合并表格第一列列数)组成的字典数据，
    如{(2, 1): (2, 1), (3, 1): (2, 1), (4, 1): (2, 1), (5, 1): (2, 1), (2, 2): (2, 2), (3, 2): (2, 2), (4, 2): (2, 2)}
    '''
    rowspan = {}
    if ws.merged_cells:
        for merge in ws.merged_cells:
            # print(merge)
            for row in range(merge.min_row, merge.max_row+1):
                for col in range(merge.min_col, merge.max_col+1):
                    rowspan.update({(row, col): (merge.min_row, merge.min_col)})
    # print(rowspan)

    # 取消合并单元格，按行读取数据
    all_new_data = []
    for row in range(2, nrows+1):
        row_dict = {}
        row_list = []
        for col in range(1, ncols):
            # 假如遇到合并的单元格坐标，取合并的首格的值即可
            if rowspan.get((row, col)):
                row_list.append(ws.cell(*rowspan.get((row, col))).value)
            else:
                row_list.append(ws.cell(row, col).value)
        # print(row_list)
        row_dict['api'] = row_list
        row_dict['count'] = ws.cell(row, ncols).value
        all_new_data.append(row_dict)

    # 将每行数据格式化为history数据类型
    user_list = get_user_list()
    all_new_data_1 = []
    for row in all_new_data:
        dict_1 = row
        other_list = [None, None]
        clientid = row['api'][1]
        clientid_list = clientid.split(',')
        # 根据clientid数据，获取对应的project，组成项目调用链
        project_link = ''
        for i in clientid_list:
            for j in range(len(user_list)):
                if i == user_list[j]['clientId']:
                    if project_link == '':
                        project_link = user_list[j]['project']
                    else:
                        project_link = project_link + '-->' + user_list[j]['project']
        other_list.append(project_link)
        # 先排除exclusion_list中无需确认的clientid，然后根据剩余clientid确认'负责人','部门','主负责人'
        new_clientid_list = []
        for i in clientid_list:
            if i not in exclusion_list:
                new_clientid_list.append(i)
        new_clientid_list.reverse()
        for i in range(len(user_list)):
            if new_clientid_list[0] == user_list[i].get('clientId'):
                other_list.extend([user_list[i].get('owner'), None, None, user_list[i].get('department'), user_list[i].get('owner'), None])
        dict_1['other'] = other_list
        dict_1['new_count'] = None
        dict_1['state'] = '新发现'
        all_new_data_1.append(dict_1)
    # print(all_new_data_1)
    return all_new_data_1


# 读取历史确认后的接口调用敏感信息数据，格式化为最终表格数据格式，输出list数据
def history_data(sheet_index):
    ws = wb.worksheets[sheet_index]
    nrows = ws.max_row
    ncols = ws.max_column
    # 按行读取数据，然后输出固定的list格式数据
    all_history_data = []
    for row in range(2, nrows+1):
        row_dict = {}
        api_list = []
        for col in range(1, 4):
            api_list.append(ws.cell(row, col).value)
        count = ws.cell(row, 4).value
        other_list = []
        for col in range(5, ncols-1):
            other_list.append(ws.cell(row, col).value)
        new_count = ws.cell(row, ncols-1).value
        row_dict['api'] = api_list
        row_dict['count'] = count
        row_dict['other'] = other_list
        row_dict['new_count'] = new_count
        row_dict['state'] = '存量'
        all_history_data.append(row_dict)
    # print('row_dict', row_dict)
    # print('all_history_data', len(all_history_data), all_history_data)
    return all_history_data


# 对比history和new数据，去重并记录相关字段数据，保存到新sheet中
def unique_data(history_index, new_index):
    history_data_0 = history_data(history_index)
    history_data_1 = []
    new_data_0 = new_data(new_index)
    new_data_1 = new_data_0
    for i in history_data_0:
        for j in new_data_0:
            if j['api'] == i['api']:
                new_data_1.remove(j)
                if i['other'][1] == None:
                    pass
                elif j['count'] > i['other'][1]:
                    i['new_count'] = j['count']
        history_data_1.append(i)
        all_date = history_data_1 + new_data_1
    print('new_data_1', len(new_data_1), new_data_1)
    print('history_data_1', len(history_data_1), history_data_1)
    print('all_data', len(all_date), all_date)

    # 将最终数据保存到新sheet中
    summary_sheet = wb.create_sheet('new_summary')
    summary_sheet.append(
        ['servletPath', 'clientId', 'initiateUrl', 'count', '说明超过1万次调用的原因', '阈值上限', 'project', '负责人', '描述', '是否合理',
         '部门', '主负责人', '说明超过阈值的原因', 'new_count', '存量/新发现'])
    for i in all_date:
        all_date_list = []
        for j in i.values():
            if isinstance(j, list):
                all_date_list.append(j)
            else:
                all_date_list.append([j])
        all_data_list_1 =[]
        for k in all_date_list:
            all_data_list_1 += k
        print('all_data_list_1', len(all_data_list_1), all_data_list_1)
        # 写入每列数据到新建的sheet中
        summary_sheet.append(all_data_list_1)
        # summary_sheet.freeze_panes = 'A2'
    wb.save(file)


if __name__ == '__main__':
    file = '敏感信息调用-2021年6月29日.xlsx'
    wb = openpyxl.load_workbook(file)
    start = datetime.datetime.now()
    # new_data(1)
    # history_data(0)
    unique_data(0, 1)
    end = datetime.datetime.now()
    print('运行时间统计：', (end - start).seconds)
